-------------------------------------------------------------------------------
-- TRIGGER FUNCTIONS
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- trg_ass_a_iu_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_ass_a_iu_val () 
RETURNS trigger AS
$body$
DECLARE
	v_ass_target_elm_Id BIGINT;
	v_state pck_state.state_type;
	v_todo_length INTEGER; 
BEGIN
	SELECT * INTO v_state FROM pck_state.getState();
	
	IF NOT v_state.avoid_recursion THEN
		v_state.avoid_recursion := TRUE;
		-- UPDATE tbl_state SET avoid_recursion = v_state.avoid_recursion;
		PERFORM pck_state.updateState(v_state);
		
		IF array_upper(v_state.todo, 1) > 0 THEN
			v_todo_length := array_upper(v_state.todo,1);
		ELSE
			v_todo_length := 0;
		END IF;	

		FOR i IN 1..v_todo_length LOOP
			PERFORM PCK_Validator.validateAssociation(v_state.todo[i]);
		END LOOP;
	END IF;

	RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_ass_a_ud_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_ass_a_ud_val () 
RETURNS trigger AS
$body$
BEGIN
	--PCK_Validator.validateMultiplicity(OLD.ASS_source_elm_id,OLD.ASS_cat_id,'min');
	RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_ass_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_ass_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.ASS_CREATINGDATE := now();
		NEW.ASS_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.ASS_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_ass_b_iu_sct
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_ass_b_iu_sct () 
RETURNS trigger AS
$body$
DECLARE 
	v_state pck_state.state_type;
BEGIN
	SELECT * INTO v_state FROM pck_state.getState();

	v_state.todo := v_state.empty;
	v_state.avoid_recursion := FALSE;
	
	PERFORM pck_state.updateState(v_state);

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_ass_b_iu_ste
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_ass_b_iu_ste () 
RETURNS trigger AS
$body$
DECLARE
	v_state pck_state.state_type;
BEGIN
	SELECT * INTO v_state FROM pck_state.getState();

	IF NOT v_state.avoid_recursion THEN
		v_state.todo := v_state.empty;
		-- UPDATE tbl_state SET todo = empty;
		PERFORM pck_state.updateState(v_state);
	END IF;

	IF TG_OP = 'INSERT' AND NEW.ass_source_elm_id IS NOT NULL THEN
		IF NOT v_state.avoid_recursion THEN
			IF array_upper(v_state.todo, 1) > 0 THEN
				v_state.todo[array_upper(v_state.todo, 1) + 1] := NEW.ass_Id;
			ELSE
				v_state.todo[1] := NEW.ass_Id;
			END IF;	

			-- UPDATE tbl_state SET todo = v_state.todo;
			PERFORM pck_state.updateState(v_state);
		END IF;
	END IF;
	 
	IF TG_OP = 'UPDATE' THEN
		IF NEW.ass_source_elm_id != OLD.ass_source_elm_id 
		OR NEW.ass_cat_id != OLD.ass_cat_id THEN
			IF NOT v_state.avoid_recursion THEN
				IF array_upper(v_state.todo, 1) > 0 THEN
					v_state.todo[array_upper(v_state.todo, 1) + 1] := NEW.ass_Id;
				ELSE
					v_state.todo[1] := NEW.ass_Id;
				END IF;	

				-- UPDATE tbl_state SET todo = v_state.todo;
				PERFORM pck_state.updateState(v_state);
			END IF;
		END IF;
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_aur_b_i_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_aur_b_i_pk () 
RETURNS trigger AS
$body$
BEGIN
	SELECT NEXTVAL('AUTROOT_AUR_ID_SEQ') INTO NEW.AUR_ID;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_aur_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_aur_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.AUR_CREATINGDATE := now();
		NEW.AUR_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.AUR_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_aut_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_aut_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.AUT_CREATINGDATE := now();
		NEW.AUT_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.AUT_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_aux_b_i_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_aux_b_i_pk () 
RETURNS trigger AS
$body$
BEGIN
	SELECT NEXTVAL('AUTINDEX_AUX_ID_SEQ') INTO NEW.AUX_ID;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_bvl_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_bvl_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.BVL_CREATINGDATE := now();
		NEW.BVL_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.BVL_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_bvl_b_iu_val_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_bvl_b_iu_val_pk () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		IF PCK_Validator.validateValue(NEW.BVL_SAT_ID, NEW.BVL_ELM_ID) = 0 THEN
   			PERFORM PCK_Exception.throwException(
   				'inv_sat_Id',
   				'SAT_ID:' || NEW.BVL_SAT_ID
   			);
		END IF;
	END IF;
	
	IF TG_OP = 'UPDATE' THEN
		IF OLD.BVL_SAT_ID != NEW.BVL_SAT_ID THEN
			PERFORM PCK_Exception.throwException(
				'inv_upd_sat_Id',
				'SAT_ID:' || NEW.BVL_SAT_ID
			);
		END IF;
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cat_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cat_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.CAT_CREATINGDATE := now();
		NEW.CAT_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.CAT_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cat_b_u_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cat_b_u_val () 
RETURNS trigger AS
$body$
DECLARE
	v_count BIGINT;
BEGIN
	IF NEW.CAT_MINMULTIPLICITY != OLD.CAT_MINMULTIPLICITY THEN
		PERFORM pck_validator.validateExistingMultiplicity(
			NEW.CAT_ID, 
			NEW.CAT_MINMULTIPLICITY, 
			'min', 
			NEW.CAT_RELATIONTYPE
		);
	END IF;
	
	IF NEW.CAT_MAXMULTIPLICITY != OLD.CAT_MAXMULTIPLICITY THEN
		PERFORM pck_validator.validateExistingMultiplicity(
			NEW.CAT_ID,
			NEW.CAT_MINMULTIPLICITY,
			'max',
			NEW.CAT_RELATIONTYPE
		);
	END IF;
	
	IF NEW.CAT_SOURCE_SCM_ID != OLD.CAT_SOURCE_SCM_ID THEN
		PERFORM PCK_Exception.throwException(
			'upd_cat_src_scm_nsp',
			'To:' || NEW.CAT_SOURCE_SCM_ID
		);
	END IF;

	IF NEW.CAT_TARGET_SCM_ID != OLD.CAT_TARGET_SCM_ID THEN
		IF OLD.CAT_RELATIONTYPE = 'a' THEN
			SELECT count(*) INTO v_count FROM association WHERE ass_cat_Id = NEW.cat_Id;
		ELSE
			SELECT count(*) INTO v_count FROM composition WHERE cmp_cat_Id = NEW.cat_Id;
		END IF;
		IF v_count > 0 THEN
			PERFORM PCK_Exception.throwException(
				'upd_cat_trg_src_nsp',
				'To:' || NEW.CAT_TARGET_SCM_ID
			);
		END IF;
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cmp_a_i_ind
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cmp_a_i_ind () 
RETURNS trigger AS
$body$
BEGIN
	IF NEW.cmp_source_elm_Id IS NOT NULL THEN
		PERFORM pck_authorisation.attachChildIndex(
			NEW.cmp_target_elm_Id,
			pck_authorisation.getParentAuxElm_Id(NEW.cmp_source_elm_Id)
		);
	END IF;
	
	RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cmp_a_iu_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cmp_a_iu_val () 
RETURNS trigger AS
$body$
DECLARE
	v_state pck_state.state_type;
	v_cmp_target_elm_Id BIGINT;
	v_todo_length INTEGER;
BEGIN
	SELECT * INTO v_state FROM pck_state.getState();
	
	IF NOT v_state.avoid_recursion THEN
		v_state.avoid_recursion := TRUE;
		-- UPDATE tbl_state SET avoid_recursion = v_state.avoid_recursion;
		PERFORM pck_state.updateState(v_state);

		IF array_upper(v_state.todo, 1) > 0 THEN
			v_todo_length := array_upper(v_state.todo, 1);
		ELSE
			v_todo_length := 0;
		END IF;	
		
		FOR i IN 1..v_todo_length LOOP
			PERFORM PCK_Validator.validateComposition(v_state.todo[i]);
			IF TG_OP = 'UPDATE' THEN
				SELECT cmp_target_elm_Id INTO v_cmp_target_elm_Id FROM composition 
				WHERE cmp_Id = v_state.todo[i];

				PERFORM pck_compositionpath.rebuildPath(v_cmp_target_elm_Id);
				PERFORM pck_authorisation.reBuildIndex(v_cmp_target_elm_Id);
			END IF;
		END LOOP;
	END IF;

	RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cmp_a_ud_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cmp_a_ud_val () RETURNS trigger AS
$body$
BEGIN
	IF OLD.CMP_source_elm_id IS NOT NULL THEN
		--PERFORM PCK_Validator.validateMultiplicity(OLD.CMP_source_elm_id, OLD.CMP_cat_id,'min');
	END IF;
	RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cmp_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cmp_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.CMP_CREATINGDATE := now();
		NEW.CMP_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.CMP_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cmp_b_iu_sct
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cmp_b_iu_sct () RETURNS trigger AS
$body$
DECLARE 
	v_state pck_state.state_type;
BEGIN
	SELECT * INTO v_state FROM pck_state.getState();

	v_state.avoid_recursion := FALSE;
	v_state.todo := v_state.empty;
	
--	UPDATE tbl_state SET avoid_recursion = FALSE, todo = empty;
	PERFORM pck_state.updateState(v_state);

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cmp_b_iu_ste
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cmp_b_iu_ste () 
RETURNS trigger AS
$body$
DECLARE
	v_state pck_state.state_type;
	v_row pck_compositionpath.getPath_result;
BEGIN
	IF TG_OP = 'INSERT' THEN
		IF NEW.cmp_key IS NULL THEN
			NEW.cmp_key := NEW.cmp_id;
		END IF;

		SELECT * INTO v_row 
		FROM pck_compositionPath.getBuildedPath(
			NEW.CMP_SOURCE_ELM_ID,
			NEW.CMP_CAT_ID,
			NEW.CMP_TARGET_ELM_ID
		);
		
		NEW.CMP_PATH  := v_row.path;
		NEW.CMP_LEVEL := v_row.level;
		 
		SELECT * INTO v_state FROM pck_state.getState();
		
		IF NOT v_state.avoid_recursion THEN
			IF array_upper(v_state.todo, 1) > 0 THEN
				v_state.todo[array_upper(v_state.todo, 1) + 1] := NEW.cmp_Id;
			ELSE
				v_state.todo[1] := NEW.cmp_Id;
			END IF;	

			-- UPDATE tbl_state SET todo = v_state.todo;
			PERFORM pck_state.updateState(v_state);
		END IF;
	END IF;

	IF TG_OP = 'UPDATE' THEN 
		IF NEW.cmp_source_elm_id != OLD.cmp_source_elm_id 
		OR NEW.cmp_cat_id != OLD.cmp_cat_id THEN

			SELECT * INTO v_state FROM pck_state.getState();
		
			IF NOT v_state.avoid_recursion THEN
				IF array_upper(v_state.todo, 1) > 0 THEN
					v_state.todo[array_upper(v_state.todo, 1) + 1] := NEW.cmp_Id;
				ELSE
					v_state.todo[1] := NEW.cmp_Id;
				END IF;	
	
				-- UPDATE tbl_state SET todo = v_state.todo;
				PERFORM pck_state.updateState(v_state);
			END IF;
		END IF;
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cvl_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cvl_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.CVL_CREATINGDATE := now();
		NEW.CVL_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.CVL_MODIFYINGDATE := now();
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_cvl_b_iu_val_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_cvl_b_iu_val_pk () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		IF PCK_Validator.validateValue(NEW.CVL_SAT_ID, NEW.CVL_ELM_ID) = 0 THEN
			PERFORM PCK_Exception.throwException(
				'inv_sat_Id',
				'SAT_ID:' || NEW.CVL_SAT_ID
			);
		END IF;
	END IF;
	
	IF TG_OP = 'UPDATE' THEN
		IF OLD.CVL_SAT_ID != NEW.CVL_SAT_ID THEN
			PERFORM PCK_Exception.throwException(
				'inv_upd_sat_Id',
				'SAT_ID:' || NEW.CVL_SAT_ID
			);
		END IF;
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_dat_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_dat_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.DAT_CREATINGDATE := now();
		NEW.DAT_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.DAT_MODIFYINGDATE := now();
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_dat_b_u_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_dat_b_u_val () 
RETURNS trigger AS
$body$
DECLARE
	v_simplevalue_count BIGINT;
	v_uniquevalue_count BIGINT;
BEGIN
	IF NEW.DAT_TYPE != OLD.DAT_TYPE THEN
		IF NEW.DAT_TYPE ='date' 
		OR NEW.DAT_TYPE ='integer' 
		OR NEW.DAT_TYPE ='money' 
		OR NEW.DAT_TYPE ='float' THEN
			SELECT count(*) INTO v_simplevalue_count FROM simplevalue 
			WHERE svl_dat_Id = NEW.dat_Id;
			
			SELECT count(*) INTO v_uniquevalue_count FROM uniqueValue 
			WHERE uvl_dat_Id = NEW.dat_Id;
			
			IF v_simplevalue_count = 1 OR v_uniquevalue_count = 1 THEN
				PERFORM PCK_Exception.throwException('upd_DAT_type_nsp', NEW.dat_type);
			END IF;
		END IF;
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_den_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_den_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.DEN_CREATINGDATE := now();
		NEW.DEN_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.DEN_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_elm_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_elm_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.ELM_CREATINGDATE := now();
		NEW.ELM_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.ELM_MODIFYINGDATE := now();
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_exc_b_i_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_exc_b_i_pk () 
RETURNS trigger AS
$body$
BEGIN
	SELECT NEXTVAL('EXCEPTION_EXC_ID_SEQ') INTO NEW.EXC_ID;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_lck_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_lck_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.LCK_CREATINGDATE := now();
		NEW.LCK_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.LCK_MODIFYINGDATE := now();
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_nsp_b_i_pat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_nsp_b_i_pat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.nsp_path := 
			pck_namespacepath.getBuildedPath(NEW.nsp_parent_nsp_id, NEW.nsp_name);
	END IF;

	IF TG_OP = 'UPDATE' THEN
		IF NEW.nsp_parent_nsp_Id != OLD.nsp_parent_nsp_Id THEN
			PERFORM pck_namespacepath.rebuildPath();
		END IF;
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_nsp_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_nsp_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.NSP_CREATINGDATE := now();
		NEW.NSP_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.NSP_MODIFYINGDATE := now();
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_ocf_b_i_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_ocf_b_i_pk () 
RETURNS trigger AS
$body$
BEGIN
	SELECT NEXTVAL('OMSCONFIG_OCF_ID_SEQ') INTO NEW.OCF_ID;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_olg_b_i_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_olg_b_i_pk () 
RETURNS trigger AS
$body$
BEGIN
	SELECT NEXTVAL('OMSLOG_OLG_ID_SEQ') INTO NEW.OLG_ID;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_olg_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_olg_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.olg_logdate := now();
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_ove_b_i_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_ove_b_i_pk () 
RETURNS trigger AS
$body$
BEGIN
	SELECT NEXTVAL('OMSVERSION_OVE_ID_SEQ') INTO NEW.OVE_ID;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_sat_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_sat_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.SAT_CREATINGDATE := now();
		NEW.SAT_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.SAT_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_sat_b_u_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_sat_b_u_val () 
RETURNS trigger AS
$body$
DECLARE
	v_count BIGINT;
BEGIN
	IF NEW.sat_scm_Id != OLD.sat_scm_Id THEN
		PERFORM PCK_Exception.throwException(
			'upd_sat_src_scm_nsp',
			'To:' || NEW.sat_scm_Id
		);
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_scm_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_scm_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.SCM_CREATINGDATE := now();
		NEW.SCM_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.SCM_MODIFYINGDATE := now();
	END IF;
	
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_scm_b_u_val
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_scm_b_u_val () 
RETURNS trigger AS
$body$
DECLARE
	v_count BIGINT;
BEGIN
	IF OLD.scm_parent_scm_Id IS NOT NULL THEN
		IF OLD.scm_parent_scm_Id != NEW.scm_parent_scm_Id THEN
			PERFORM pck_exception.throwexception('upd_inheritance_nsp', '');
		
			SELECT count(*) INTO v_count FROM element WHERE elm_scm_Id = OLD.scm_Id;

			IF v_count > 0 THEN
				PERFORM PCK_Exception.throwException(
					'del_inheritance_nsp', 
					'scm_ID: ' || OLD.scm_Id
				);
			END IF;
		END IF;
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_svl_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_svl_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.SVL_CREATINGDATE := now();
		NEW.SVL_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.SVL_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_svl_b_iu_val_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_svl_b_iu_val_pk () 
RETURNS trigger AS
$body$
DECLARE
	v_type varchar(50);
BEGIN
	SELECT dat_type INTO v_type FROM dataType WHERE dat_Id = NEW.SVL_DAT_ID;

	IF PCK_Validator.validateDataType(v_type, NEW.svl_value) = 0 THEN
		PERFORM PCK_Exception.throwException(
			'inv_value',
			'type:' || v_type || '; value: ' || NEW.svl_value
		);
	END IF;

	IF TG_OP = 'INSERT' THEN
		IF PCK_Validator.validateValue(NEW.SVL_SAT_ID, NEW.SVL_ELM_ID) = 0 THEN
			PERFORM PCK_Exception.throwException(
				'inv_sat_Id',
				'SAT_ID:' || NEW.SVL_SAT_ID
			);
		END IF;
	END IF;

	IF TG_OP = 'UPDATE' THEN
		IF OLD.SVL_SAT_ID != NEW.SVL_SAT_ID THEN
			PERFORM PCK_Exception.throwException(
				'inv_upd_sat_Id',
				'SAT_ID:' || NEW.SVL_SAT_ID
			);
		END IF;
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_uvl_b_iu_dat
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_uvl_b_iu_dat () 
RETURNS trigger AS
$body$
BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.UVL_CREATINGDATE := now();
		NEW.UVL_MODIFYINGDATE := now();
	END IF;

	IF TG_OP = 'UPDATE' THEN
		NEW.UVL_MODIFYINGDATE := now();
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

-------------------------------------------------------------------------------
-- trg_uvl_b_iu_val_pk
-------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trigger_procs.trg_uvl_b_iu_val_pk () 
RETURNS trigger AS
$body$
DECLARE
	v_type varchar(50);
BEGIN
	SELECT dat_type INTO v_type FROM dataType WHERE dat_Id = NEW.UVL_DAT_ID;

	IF PCK_Validator.validateDataType(v_type, NEW.uvl_value) = 0 THEN
		PERFORM PCK_Exception.throwException(
			'inv_value',
			'type:' || v_type || '; value: ' || NEW.uvl_value
		);
	END IF;

	IF TG_OP = 'INSERT' THEN
		IF PCK_Validator.validateValue(NEW.UVL_SAT_ID, NEW.UVL_ELM_ID) = 0 THEN
			PERFORM PCK_Exception.throwException('inv_sat_Id', 'SAT_ID:' || NEW.UVL_SAT_ID);
		END IF;
	END IF;
   
	IF TG_OP = 'UPDATE' THEN
		IF OLD.UVL_SAT_ID != NEW.UVL_SAT_ID THEN
			PERFORM PCK_Exception.throwException(
				'inv_upd_sat_Id', 
				'SAT_ID:' || NEW.UVL_SAT_ID
			);
		END IF;
	END IF;

	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

